// backend/recompute/jobs.cjs
// New, clean implementation for recompute jobs, extracted from server-simple.cjs
// Adheres to production rules: no mock, try/catch+logs+return codes, env defaults, absolute imports

const { getPeopleExpr, getMaintenanceExpr, getTargetColsPresence } = require('../../check-staff-fields.cjs');

// 使用传入的 dbPool 实例，避免重复创建
let dbPool = null;

function nowStamp() {
  const d = new Date();
  const pad = n => String(n).padStart(2, '0');
  return `${d.getFullYear()}${pad(d.getMonth()+1)}${pad(d.getDate())}${pad(d.getHours())}${pad(d.getMinutes())}${pad(d.getSeconds())}`;
}

function genJobId(){ return `${nowStamp()}-${Math.random().toString(36).slice(2,10)}` }
function sleep(ms){ return new Promise(r=>setTimeout(r, ms)) }

function buildWhereClauseForRecompute(filter){
  const where=[]; const params=[];
  const f = filter||{};
  if (f.record_date) { where.push('record_date = ?'); params.push(f.record_date); }
  if (f.order_number) { where.push('TRIM(order_number) = TRIM(?)'); params.push(f.order_number); }
  if (f.from_date) { where.push('record_date >= ?'); params.push(f.from_date); }
  if (f.to_date) { where.push('record_date <= ?'); params.push(f.to_date); }
  if (f.line_name) { where.push('TRIM(line_name) = TRIM(?)'); params.push(f.line_name); }
  if (Array.isArray(f.line_names) && f.line_names.length){ const marks=f.line_names.map(()=>'?').join(','); where.push(`TRIM(line_name) IN (${marks})`); params.push(...f.line_names); }
  if (f.material_code) { where.push('UPPER(material_code) = UPPER(?)'); params.push(f.material_code); }
  if (Array.isArray(f.material_codes) && f.material_codes.length){ const marks=f.material_codes.map(()=>'?').join(','); where.push(`UPPER(material_code) IN (${marks})`); params.push(...f.material_codes.map(x=>String(x).toUpperCase())); }
  return { whereSql: where.length?('WHERE '+where.join(' AND ')):'' , params };
}

function normalizeFilterForDedup(f){
  try{
    const x = f || {};
    const norm = {};
    if (x.from_date) norm.from_date = String(x.from_date).trim();
    if (x.to_date) norm.to_date = String(x.to_date).trim();
    const recalcDefault = String(process.env.RECOMPUTE_DEFAULT_RECALC_PPH || (x.recalc_pph===undefined? '1' : x.recalc_pph));
    norm.recalc_pph = (recalcDefault === '1') ? '1' : '0';
    if (Array.isArray(x.line_names)) norm.line_names = [...x.line_names].map(s=>String(s).trim()).sort(); else norm.line_names = [];
    if (Array.isArray(x.material_codes)) norm.material_codes = [...x.material_codes].map(s=>String(s).toUpperCase().trim()).sort(); else norm.material_codes = [];
    // ensure stable key order
    const ordered = {};
    Object.keys(norm).sort().forEach(k=>ordered[k]=norm[k]);
    return ordered;
  }catch(e){
    try{ console.error('normalizeFilterForDedup failed:', e && (e.message||e)); }catch(_){ }
    return {};
  }
}

function initializeDbPool(pool) {
  dbPool = pool;
}

async function startRecomputeJob(filter, pool = null){
  if (pool) dbPool = pool;
  if (!dbPool) throw new Error('Database pool not initialized');

  try{
    // 清空目标/预估缓存，避免使用旧口径数据（例如调整目标生效日后立即重算）
    try { targetCache.clear(); console.log('🧹 已清空目标/预估缓存'); } catch (_) {}
    const allowAll = String(process.env.ALLOW_RECOMPUTE_ALL || '1') === '1';
    const hasAnyFilter = !!(filter && (filter.record_date || filter.order_number || filter.from_date || filter.to_date || filter.line_name || filter.material_code || (filter.line_names && filter.line_names.length) || (filter.material_codes && filter.material_codes.length)));
    if (!hasAnyFilter && !allowAll){ throw new Error('未提供筛选条件且未启用全量重算(ALLOW_RECOMPUTE_ALL=1)'); }

    const normFilter = normalizeFilterForDedup(filter||{});
    // 强化重复检测：检查所有运行中的任务，不仅仅是相同filter的
    const runningRows = await dbPool.query(`SELECT id,status,filter_json FROM recompute_jobs WHERE status IN ('queued','running') ORDER BY created_at DESC`);
    if (runningRows && runningRows.length > 0) {
      console.log(`⚠️ 检测到 ${runningRows.length} 个运行中的任务，拒绝启动新任务`);
      return { job_id: runningRows[0].id, total: 0, deduped: true, message: `已有${runningRows.length}个任务运行中，请等待完成后再启动` };
    }

    // 额外检查：清理状态异常的任务（有finished_at但状态仍为running）
    await dbPool.query(`UPDATE recompute_jobs SET status='failed', error='状态异常：已完成但状态未更新' WHERE status='running' AND finished_at IS NOT NULL`);

    const dupRows = await dbPool.query(`SELECT id,status FROM recompute_jobs WHERE (status IN ('queued','running')) AND filter_json = ? ORDER BY created_at DESC LIMIT 1`, [JSON.stringify(normFilter)]);
    if (dupRows && dupRows.length){ return { job_id: dupRows[0].id, total: 0, deduped: true }; }

    const jobId = genJobId();
    const { whereSql, params } = buildWhereClauseForRecompute(filter||{});
    const totalRow = await dbPool.query(`SELECT COUNT(1) AS cnt FROM pph_records ${whereSql}`, params).then(r=>r&&r[0]||{cnt:0});
    const total = Number(totalRow.cnt||0);

    // 高性能批次大小：大幅提升处理速度
    const defaultBatchSize = total > 50000 ? 2000 : (total > 10000 ? 1500 : 1000); // 大批次处理
    const batchSize = parseInt(process.env.RECOMPUTE_BATCH_SIZE || String(filter && filter.batch_size || defaultBatchSize));
    await dbPool.query(`INSERT INTO recompute_jobs (id,status,total,processed,updated,batch_size,filter_json) VALUES (?,?,?,?,?,?,?)`, [jobId,'queued',total,0,0,batchSize, JSON.stringify(normFilter)]);
    runRecomputeJob(jobId).catch(e=>{try{console.error('后台重算任务失败:', e && (e.message||e));}catch(_){}});
    return { job_id: jobId, total };
  }catch(e){
    try{ console.error('startRecomputeJob 失败:', e && (e.message||e)); }catch(_){ }
    throw e;
  }
}

async function runRecomputeJob(jobId){
  try{
    const jobs = await dbPool.query('SELECT * FROM recompute_jobs WHERE id=?',[jobId]);
    const job = jobs[0]; if (!job) throw new Error('任务不存在');
    const filter = job.filter_json ? JSON.parse(job.filter_json) : {};
    const batchSize = Number(job.batch_size||500);

    await dbPool.query(`UPDATE recompute_jobs SET status='running', started_at=CURRENT_TIMESTAMP WHERE id=?`,[jobId]);
    const { whereSql, params } = buildWhereClauseForRecompute(filter||{});

    const hasQty = await columnExists('pph_records','quantity');
    const hasCompletedQty = await columnExists('pph_records','completed_quantity');
    const qtyExpr = hasQty ? 'quantity' : (hasCompletedQty ? 'completed_quantity' : '0');

    const pe = await getPeopleExpr(dbPool); const peopleExpr = pe.expr;
    const me = await getMaintenanceExpr(dbPool); const maintExpr = me.expr;
    const tc = await getTargetColsPresence(dbPool); const hasTargetCol = tc.hasTarget, hasEstimateCol = tc.hasEstimate, hasEffCol = tc.hasEfficiency;

    const totalRow = await dbPool.query(`SELECT COUNT(1) AS cnt FROM pph_records ${whereSql}`, params).then(r=>r&&r[0]||{cnt:0});
    const total = Number(totalRow.cnt||0);

    // 高性能模式：移除节流，使用批量处理
    const throttleMs = 0; // 不再需要节流
    console.log(`📊 重算任务：总记录${total}条，批次大小${batchSize}，节流时间${throttleMs}ms`);

    let offset = 0; let processed = 0; let updated = 0;
    while (processed < total){
      const jobRows = await dbPool.query('SELECT status FROM recompute_jobs WHERE id=?',[jobId]);
      if (!jobRows.length || jobRows[0].status === 'cancelled') break;

      const sel = `SELECT id, record_date, line_name, material_code, is_whole_machine, calculated_pph,
                          start_time, end_time, ${qtyExpr} AS quantity, ${peopleExpr} AS total_people,
                          COALESCE(break_minutes,0) AS break_minutes,
                          COALESCE(abnormal_loss_minutes,0) AS abnormal_loss_minutes,
                          COALESCE(changeover_minutes,0) AS changeover_minutes,
                          ${maintExpr} AS maintenance_time,
                          net_working_minutes, actual_takt_seconds
                   FROM pph_records ${whereSql}
                   ORDER BY record_date, line_name, material_code
                   LIMIT ${batchSize} OFFSET ${offset}`;
      const rows = await dbPool.query(sel, params);

      // 🚀 高性能批量处理：一次性获取所有目标值和预估值
      const batchResults = await batchGetTargetsAndEstimates(rows);

      let batchUpdated = 0;
      for (const row of rows){
        try{
          let target_pph=null, target_record_date=null, estimate_pph=null, efficiency_rate=null;
          let target_takt_time=null, target_motor_takt_time=null;

          // 从批量查询结果中获取数据
          const batchResult = batchResults.get(row.id);
          if (batchResult) {
            target_pph = batchResult.target_pph;
            target_record_date = batchResult.target_record_date;
            target_takt_time = batchResult.target_takt_time;
            target_motor_takt_time = batchResult.target_motor_takt_time;
            estimate_pph = batchResult.estimate_pph;

            const baseline = (target_pph!=null && target_pph>0) ? target_pph : ((estimate_pph!=null && estimate_pph>0)?estimate_pph:null);
            if (baseline!=null && baseline>0 && row.calculated_pph!=null){
              efficiency_rate = Math.round(((Number(row.calculated_pph)/baseline)*100)*100)/100;
            }
          }

          let net_working_minutes=null, actual_takt_seconds=null, newCalculatedPPH=row.calculated_pph;
          if (row && row.start_time && row.end_time && row.total_people){
            const [sh,sm]=String(row.start_time).split(':').map(Number);
            const [eh,em]=String(row.end_time).split(':').map(Number);
            const sMin = sh*60+sm; let eMin=eh*60+em; if (eMin<sMin) eMin+=24*60;
            const totalMinutes = eMin - sMin;
            const lossMinutes = Number(row.break_minutes||0)+Number(row.abnormal_loss_minutes||0)+Number(row.changeover_minutes||0)+Number(row.maintenance_time||0);
            net_working_minutes = Math.max(0, totalMinutes - lossMinutes);
            const netHours = net_working_minutes/60;
            const qty = Number(row.quantity||0);
            const ppl = Math.max(1, Number(row.total_people||1));
            if (qty>0 && net_working_minutes>0){ actual_takt_seconds = Math.round((net_working_minutes/qty)*60*100)/100 }
            if (String(filter.recalc_pph||'0')==='1' && netHours>0 && ppl>0){ newCalculatedPPH = Math.round((qty/(netHours*ppl))*10)/10 }
          }

          const fields=[], vals=[];
          if (String(filter.recalc_pph||'0')==='1'){ fields.push('calculated_pph = ?'); vals.push(newCalculatedPPH) }
          if (hasTargetCol) { fields.push('target_pph = ?'); vals.push(target_pph); }
          fields.push('target_record_date = ?'); vals.push(target_record_date);
          if (hasEstimateCol) { fields.push('estimate_pph = ?'); vals.push(estimate_pph); }
          if (hasEffCol) { fields.push('efficiency_rate = ?'); vals.push(efficiency_rate); }
          fields.push('net_working_minutes = ?'); vals.push(net_working_minutes);
          fields.push('actual_takt_seconds = ?'); vals.push(actual_takt_seconds);
          fields.push('target_takt_time = ?'); vals.push(target_takt_time);
          fields.push('target_motor_takt_time = ?'); vals.push(target_motor_takt_time);
          fields.push('updated_at = CURRENT_TIMESTAMP');
          const upSql = `UPDATE pph_records SET ${fields.join(', ')} WHERE id = ?`;
          vals.push(row.id);
          await dbPool.query(upSql, vals);
          batchUpdated++;
        }catch(e){ try{ console.warn('重算单行失败(id='+row.id+'):', e && (e.message||e)); }catch(_){ } }
      }

      processed += rows.length; updated += batchUpdated; offset += rows.length;
      await dbPool.query('UPDATE recompute_jobs SET processed=?, updated=?, status=? WHERE id=?', [processed, updated, 'running', jobId]);
      if (throttleMs>0) await sleep(throttleMs);
      if (rows.length < batchSize) break;
    }

    await dbPool.query(`UPDATE recompute_jobs SET status='completed', finished_at=CURRENT_TIMESTAMP, processed=?, updated=? WHERE id=?`, [processed, updated, jobId]);
  }catch(e){
    try{ console.error('runRecomputeJob失败:', e && (e.message||e)); }catch(_){ }
    try{ await dbPool.query(`UPDATE recompute_jobs SET status='failed', error=?, finished_at=CURRENT_TIMESTAMP WHERE id=?`, [e && e.message, jobId]) }catch(_){ }
  }
}

async function columnExists(table, column){
  try{
    const rows = await dbPool.query(`PRAGMA table_info(${table})`);
    return rows.some(r=>String(r.name).toLowerCase()===String(column).toLowerCase());
  }catch(_){ return false; }
}

// 速率限制器
class RateLimiter {
  constructor(maxRequests = 50, timeWindow = 60000) { // 每分钟50次请求
    this.requests = [];
    this.maxRequests = maxRequests;
    this.timeWindow = timeWindow;
  }

  async wait() {
    const now = Date.now();
    // 清理过期请求
    this.requests = this.requests.filter(time => now - time < this.timeWindow);

    if (this.requests.length >= this.maxRequests) {
      const oldestRequest = this.requests[0];
      const waitTime = this.timeWindow - (now - oldestRequest);
      if (waitTime > 0) {
        console.log(`⏳ 速率限制：等待 ${Math.ceil(waitTime/1000)} 秒...`);
        await sleep(waitTime);
      }
    }

    this.requests.push(now);
  }
}

// 高性能LRU缓存系统 (基于KIMI建议)
const crypto = require('crypto');

class TargetCache {
  constructor() {
    // 使用内置LRU缓存替代Map，避免内存泄漏
    this.cache = new Map();
    this.maxSize = 10000; // 增加缓存容量应对大量数据
    this.ttl = 2 * 60 * 60 * 1000; // 2小时TTL - 确保重算任务期间不过期
    this.accessOrder = new Map(); // 跟踪访问顺序实现LRU
    this.hitCount = 0;
    this.missCount = 0;
  }

  // 简化键生成，避免MD5哈希问题
  getKey(material_code, line_name, record_date, is_whole_machine) {
    // 使用JSON.stringify确保键的一致性
    return JSON.stringify([
      String(material_code || '').toUpperCase().trim(),
      String(line_name || '').trim(),
      String(record_date || ''),
      Boolean(is_whole_machine)
    ]);
  }

  get(material_code, line_name, record_date, is_whole_machine) {
    const key = this.getKey(material_code, line_name, record_date, is_whole_machine);
    const item = this.cache.get(key);

    if (item && Date.now() - item.timestamp < this.ttl) {
      // 更新访问时间实现LRU
      this.accessOrder.set(key, Date.now());
      this.hitCount++;
      return item.data;
    }

    if (item) {
      this.cache.delete(key);
      this.accessOrder.delete(key);
    }
    this.missCount++;
    return null;
  }

  set(material_code, line_name, record_date, is_whole_machine, data) {
    const key = this.getKey(material_code, line_name, record_date, is_whole_machine);

    // LRU清理策略：删除最久未访问的项
    if (this.cache.size >= this.maxSize) {
      const oldestKey = this.accessOrder.keys().next().value;
      if (oldestKey) {
        this.cache.delete(oldestKey);
        this.accessOrder.delete(oldestKey);
      }
    }

    this.cache.set(key, { data, timestamp: Date.now() });
    this.accessOrder.set(key, Date.now());
  }

  clear() {
    this.cache.clear();
    this.accessOrder.clear();
  }

  // 添加缓存统计信息
  getStats() {
    return {
      size: this.cache.size,
      maxSize: this.maxSize,
      hitRate: this.hitCount / (this.hitCount + this.missCount) || 0
    };
  }
}

const targetCache = new TargetCache();

// 单个目标值查询
async function querySingleTarget(material_code, line_name, record_date, is_whole_machine) {
  try {
    const sql = `
      SELECT material_code, line_name, record_date,
             motor_target, whole_machine_target
      FROM pph_targets
      WHERE UPPER(material_code) = UPPER(?)
        AND TRIM(line_name) = TRIM(?)
        AND record_date <= ?
        AND is_active = 1
      ORDER BY record_date DESC
      LIMIT 1
    `;

    const rows = await dbPool.query(sql, [material_code, line_name, record_date]);

    if (rows.length > 0) {
      const row = rows[0];
      const target_pph = is_whole_machine ? row.whole_machine_target : row.motor_target;
      return {
        target_pph: target_pph ? Number(target_pph) : null,
        target_record_date: row.record_date
      };
    }

    return { target_pph: null, target_record_date: null };
  } catch (error) {
    console.error('单个目标值查询失败:', error);
    return { target_pph: null, target_record_date: null };
  }
}

// 单个预估值查询
async function querySingleEstimate(material_code) {
  try {
    const sql = `
      SELECT material_code, estimate_pph
      FROM pph_estimates
      WHERE UPPER(material_code) = UPPER(?)
      LIMIT 1
    `;

    const rows = await dbPool.query(sql, [material_code]);

    if (rows.length > 0) {
      return {
        estimate_pph: rows[0].estimate_pph ? Number(rows[0].estimate_pph) : null
      };
    }

    return { estimate_pph: null };
  } catch (error) {
    console.error('单个预估值查询失败:', error);
    return { estimate_pph: null };
  }
}

// 移除速率限制器，改用批量查询
// const rateLimiter = new RateLimiter(20, 60000);

// 高性能批量查询函数
async function batchGetTargetsAndEstimates(records) {
  const results = new Map();
  const uniqueQueries = new Map();

  // 1. 检查缓存并收集需要查询的唯一组合
  let cacheHitCount = 0;
  let cacheMissCount = 0;

  for (const record of records) {
    const { material_code, line_name, record_date, is_whole_machine } = record;

    // 先检查缓存
    const cached = targetCache.get(material_code, line_name, record_date, is_whole_machine);
    if (cached) {
      results.set(record.id, cached);
      cacheHitCount++;
      continue;
    }
    cacheMissCount++;

    // 收集需要查询的唯一组合
    const queryKey = targetCache.getKey(material_code, line_name, record_date, is_whole_machine);

    if (!uniqueQueries.has(queryKey)) {
      uniqueQueries.set(queryKey, { material_code, line_name, record_date, is_whole_machine, records: [] });
    }
    uniqueQueries.get(queryKey).records.push(record);
  }

  // 如果所有记录都有缓存结果，直接返回
  if (results.size === records.length) {
    const stats = targetCache.getStats();
    const batchHitRate = (cacheHitCount / records.length * 100).toFixed(1);
    console.log(`🚀 全缓存命中：${records.length} 条记录，命中率 ${batchHitRate}%`);
    return results;
  }

  // 批量查询目标值和预估值
  const targetQueries = Array.from(uniqueQueries.values());
  const aggregationRatio = records.length / targetQueries.length;
  console.log(`🚀 批量查询：${records.length} 条记录聚合为 ${targetQueries.length} 个唯一组合，聚合比例 ${aggregationRatio.toFixed(1)}:1`);

  const targetResults = await batchQueryTargets(targetQueries);

  // 恢复预估值查询 - 预估值数据少是因为生成逻辑有问题，需要修复
  const estimateResults = await batchQueryEstimates(targetQueries);

  // 合并结果并更新缓存
  for (const query of targetQueries) {
    const { material_code, line_name, record_date, is_whole_machine, records: queryRecords } = query;
    const queryKey = targetCache.getKey(material_code, line_name, record_date, is_whole_machine);

    const target = targetResults.get(queryKey) || {};
    const estKey = `${String(material_code).toUpperCase().trim()}||${String(line_name).trim()}`;
    const estimate = estimateResults.get(estKey) || {};

    const result = {
      target_pph: target.target_pph || null,
      estimate_pph: estimate.estimate_pph || null,
      target_record_date: target.target_record_date || null,
      target_takt_time: target.target_takt_time || null,
      target_motor_takt_time: target.target_motor_takt_time || null
    };

    // 立即设置缓存
    targetCache.set(material_code, line_name, record_date, is_whole_machine, result);

    // 为所有相同查询的记录设置结果
    for (const record of queryRecords) {
      results.set(record.id, result);
    }
  }

  // 最终统计
  const stats = targetCache.getStats();
  const batchHitRate = (cacheHitCount / records.length * 100).toFixed(1);

  // 统计无值记录
  let nullTargetCount = 0;
  let nullEstimateCount = 0;
  let totalNullCount = 0;

  for (const result of results.values()) {
    if (result.target_pph === null) nullTargetCount++;
    if (result.estimate_pph === null) nullEstimateCount++;
    if (result.target_pph === null && result.estimate_pph === null) totalNullCount++;
  }

  const nullTargetRate = (nullTargetCount / records.length * 100).toFixed(1);
  const nullEstimateRate = (nullEstimateCount / records.length * 100).toFixed(1);
  const totalNullRate = (totalNullCount / records.length * 100).toFixed(1);

  console.log(`📊 缓存统计：命中率 ${batchHitRate}%，缓存大小 ${stats.size}/${stats.maxSize}，节省 ${cacheHitCount} 次查询`);
  console.log(`📊 数据统计：目标值缺失 ${nullTargetRate}%，预估值缺失 ${nullEstimateRate}%，完全无值 ${totalNullRate}%`);



  return results;
}

// 批量查询目标值
async function batchQueryTargets(queries) {
  const results = new Map();

  if (queries.length === 0) return results;

  try {
    // 构建批量查询SQL
    const conditions = queries.map(() =>
      '(UPPER(material_code) = UPPER(?) AND TRIM(line_name) = TRIM(?) AND record_date <= ?)'
    ).join(' OR ');

    const params = [];
    for (const query of queries) {
      params.push(query.material_code, query.line_name, query.record_date);
    }

    const sql = `
      SELECT material_code, line_name, record_date,
             motor_target, whole_machine_target, takt_time, motor_takt_time
      FROM pph_targets
      WHERE (${conditions}) AND is_active = 1
      ORDER BY material_code, line_name, record_date DESC
    `;

    const rows = await dbPool.query(sql, params);

    // 处理结果：为每个查询找到最新的目标值
    for (const query of queries) {
      const { material_code, line_name, record_date, is_whole_machine } = query;
      const queryKey = targetCache.getKey(material_code, line_name, record_date, is_whole_machine);

      // 找到匹配的最新记录
      const matchingRow = rows.find(row =>
        row.material_code.toUpperCase() === material_code.toUpperCase() &&
        row.line_name.trim() === line_name.trim() &&
        row.record_date <= record_date
      );

      if (matchingRow) {
        const target_pph = is_whole_machine
          ? matchingRow.whole_machine_target
          : matchingRow.motor_target;

        // 改进目标值处理：null和undefined保持为null，有效数值转换为Number
        let finalTargetPph = null;
        if (target_pph !== null && target_pph !== undefined && target_pph !== '') {
          const numValue = Number(target_pph);
          if (!isNaN(numValue) && numValue > 0) {
            finalTargetPph = numValue;
          }
        }

        // 如果目标值为null，记录详细信息用于调试
        if (finalTargetPph === null) {
          console.warn(`⚠️ 物料 ${material_code} 在 ${line_name} 线 ${record_date} 的目标值为空:`, {
            is_whole_machine,
            whole_machine_target: matchingRow.whole_machine_target,
            motor_target: matchingRow.motor_target,
            selected_target: target_pph
          });
        }

        // 🔧 修复：根据生产类型严格分配节拍字段，不允许跨字段获取
        let target_takt_time = null;
        let target_motor_takt_time = null;

        if (is_whole_machine === 1) {
          // 整机生产：仅使用整机节拍，如果为空则保持null
          target_takt_time = matchingRow.takt_time != null ? Number(matchingRow.takt_time) : null;
        } else {
          // 电机生产：仅使用电机节拍，如果为空则保持null（不从整机节拍获取）
          target_motor_takt_time = matchingRow.motor_takt_time != null ? Number(matchingRow.motor_takt_time) : null;
        }

        results.set(queryKey, {
          target_pph: finalTargetPph,
          target_record_date: matchingRow.record_date,
          target_takt_time: target_takt_time,
          target_motor_takt_time: target_motor_takt_time
        });
      } else {
        // 未找到匹配的目标值记录
        console.warn(`⚠️ 未找到物料 ${material_code} 在 ${line_name} 线 ${record_date} 的目标值记录`);
      }
    }
  } catch (error) {
    console.error('批量查询目标值失败:', error);
  }

  return results;
}

// 批量查询预估值
async function batchQueryEstimates(queries) {
  const results = new Map();
  if (queries.length === 0) return results;

  try {
    // 唯一 (material_code, line_name) 组合
    const pairs = new Map();
    for (const q of queries) {
      const key = `${String(q.material_code).toUpperCase().trim()}||${String(q.line_name).trim()}`;
      if (!pairs.has(key)) pairs.set(key, { material_code: q.material_code, line_name: q.line_name });
    }

    // 构建 OR 条件：(material_code,line_name) 对
    const cond = Array.from(pairs.values()).map(() => '(UPPER(material_code)=UPPER(?) AND TRIM(line_name)=TRIM(?))').join(' OR ');
    const params = [];
    for (const p of pairs.values()) { params.push(p.material_code, p.line_name); }

    const sql = `
      SELECT material_code, line_name, estimate_pph, updated_at
      FROM pph_estimates
      WHERE ${cond}
      ORDER BY material_code, line_name, updated_at DESC
    `;

    const rows = await dbPool.query(sql, params);

    // 取每个 (material_code,line_name) 最新一条
    for (const p of pairs.values()) {
      const r = rows.find(row => row.material_code.toUpperCase() === String(p.material_code).toUpperCase() && row.line_name.trim() === String(p.line_name).trim());
      if (r) {
        const key = `${String(p.material_code).toUpperCase().trim()}||${String(p.line_name).trim()}`;
        results.set(key, { estimate_pph: r.estimate_pph != null ? Number(r.estimate_pph) : null });
      }
    }
  } catch (error) {
    console.error('批量查询预估值失败:', error);
  }

  return results;
}

module.exports = {
  buildWhereClauseForRecompute,
  normalizeFilterForDedup,
  startRecomputeJob,
  runRecomputeJob,
  initializeDbPool,
};

